USE ConsultTracking
go
drop proc CreateUser
drop proc UpdateUser
drop proc CreateDoStuff
drop proc SetUserDoStuff
drop proc CanUserDoStuff
GO
-------------------------------------------------
create proc CreateUser
@Logon nvarchar(15),
@CustomerID int = null
as 
INSERT INTO [ConsultTracking].[dbo].[User]
           ([LogOnName]
           ,[CustomerID])
     VALUES
           (@Logon
           ,@CustomerID)

declare @LastUserID int
set @LastUserID = SCOPE_IDENTITY()

INSERT INTO [ConsultTracking].[dbo].[UserDoStuff]
           ([DoStuffID]
           ,[UserID]
           ,[Doable])
		select DoStuffID,@LastUserID,0 from DoStuff 

-----------------------------------------------
go
create proc UpdateUser
@UserID int,
@LogOnName nvarchar(15),
@CustomerID int = null,
@Modified smalldatetime 
as
UPDATE [ConsultTracking].[dbo].[User]
   SET [LogOnName] = @LogOnName
      ,[CustomerID] = @CustomerID
 	  ,[Modified] = GETDATE()
WHERE UserID = @UserID and Modified = @Modified
-------------------------------------------------
go
create proc CreateDoStuff
@DoStuffName nvarchar(100),
@DoStuffDescription nvarchar(300)
as
INSERT INTO [ConsultTracking].[dbo].[DoStuff]
           ([DoStuffName]
           ,[DoStuffDescription])
     VALUES
           (@DoStuffName
           ,@DoStuffDescription);

declare @LastDoStuffID int
set @LastDoStuffID = SCOPE_IDENTITY()

INSERT INTO [ConsultTracking].[dbo].[UserDoStuff]
           ([DoStuffID]
           ,[UserID]
           ,[Doable])
	select @LastDoStuffID,UserID,0 from [User]
---------------------------------------------------
GO
CREATE PROC SetUserDoStuff
@UserID int,
@DoStuffID int,
@Doable bit
as
UPDATE [ConsultTracking].[dbo].[UserDoStuff]
   SET [Doable] = @Doable
      ,[Modified] = GETDATE()
 WHERE UserID = @UserID AND DoStuffID = @DoStuffID
----------------------------------------------------
GO
Create proc CanUserDoStuff
@UserID int,
@DoStuffID int,
@UserCanDoStuff bit out
as
set @UserCanDoStuff = 
(
	select Doable from UserDoStuff 
	where UserID = @UserID and DoStuffID = @DoStuffID
)

--declare @userID int
--declare @DoStuffID int
--declare @UserCanDoStuff bit
--set @UserID = 1
--set @DoStuffID = 1
--
--exec CanUserDoStuff @UserID, @DoStuffID, @UserCanDoStuff out
--select @UserCanDoStuff
------------------------------------------------------------------
